-- 营销活动主题插入数据
-- 开启本地模式
set hive.exec.mode.local.auto=true;
set hive.exec.mode.local.auto.inputbytes.max=500000000;

-- 将投放表与用户基本信息表关联，关联条件以设备号为准
with t1 as(
    select
        b.customer_id,
        a.device_num,
        a.device_type,
        a.os,
        a.os_version,
        a.manufacturer,
        a.area_code,
        a.release_sid,
        parse_url(concat('http://qianfeng01:8888/release?','',release_params),'QUERY','ip') as release_ip,
        a.release_session,
        a.release_sources,
        parse_url(concat('http://qianfeng01:8888/release?','',release_params),'QUERY','productPage') as release_product_page,
        a.ct
    from ods_nshop.ods_nshop_01_releasedatas a
             join ods_nshop.ods_02_customer b
                  on a.device_num=b.customer_device_num
    where bdp_day='20220510'
)
-- 将t1表数据和页面布局维表&商品信息维表关联
insert overwrite table dwd_nshop.dwd_nshop_releasedatas partition(bdp_day='20220510')
select
    t1.customer_id,
    t1.device_num,
    t1.device_type,
    t1.os,
    t1.os_version,
    t1.manufacturer,
    t1.area_code,
    t1.release_sid,
    t1.release_ip,
    t1.release_session,
    t1.release_sources,
    c.category_code,
    b.page_target,
    t1.release_product_page,
    t1.ct
from t1 join ods_nshop.dim_pub_page b
             on t1.release_product_page=b.page_code and b.page_type='4'
        join ods_nshop.dim_pub_product c
             on b.page_target=c.product_code;

select * from ods_nshop.ods_nshop_01_useractlog;
